import pandas as pd
import os
import time
import plotly.graph_objs as go
import datetime
import xlwings as xw


class Hand_time:
    def __init__(self):
        path = '/Users/user/PycharmProjects/text1/IT-pass与fail时间是否大于三分钟'
        files = os.listdir(path)
        print(files)
        self.path = None
        for i in files:
            if "if-NPI" in i and i.endswith('.csv'):
                print(i)
                self.path = os.path.join(path, i)
                print(self.path)
            elif 'it-mic-c' in i and i.endswith('.csv'):
                print(i)
                self.path = os.path.join(path, i)
                print(self.path)
            elif 'it-4e' in i and i.endswith('.csv'):
                print(i)
                self.path = os.path.join(path, i)
                print(self.path)

    def get_data(self):
        col = ['SerialNumber', 'Test Pass/Fail Status', 'StartTime']
        df = pd.read_csv(self.path, skiprows=[1, 2, 3, 4, 5], usecols=col)
        return df

    def clear_data(self):
        df = self.get_data()
        df_pass = df[df['Test Pass/Fail Status'] == 'PASS'].sort_values(by=['StartTime']).drop_duplicates(
            subset=['SerialNumber'], keep='first')
        df_fail = df[df['Test Pass/Fail Status'] == 'FAIL'].sort_values(by=['StartTime']).drop_duplicates(
            subset=['SerialNumber'], keep='last')
        mask = df_fail['SerialNumber'].values
        df_pass = df_pass[df_pass['SerialNumber'].isin(mask)]
        df_pass.index = range(df_pass.shape[0])
        df_fail.index = range(df_fail.shape[0])
        fail = df_fail.drop('Test Pass/Fail Status', axis=1)
        pas = df_pass.drop('Test Pass/Fail Status', axis=1)
        merge_ = pd.merge(pas, fail, left_on='SerialNumber', right_on='SerialNumber', how='inner')
        merge_ = merge_.rename(columns={'StartTime_x': 'pas_time'})
        merge_ = merge_.rename(columns={'StartTime_y': 'fail_time'})
        merge_['pas_time'] = pd.to_datetime(merge_['pas_time'])
        merge_['fail_time'] = pd.to_datetime(merge_['fail_time'])
        merge_['time'] = merge_['pas_time'] - merge_['fail_time']
        merge_['second'] = merge_['time'].dt.total_seconds().astype(int)
        merge_['time'] = merge_['time'].astype(str)
        merge_ = merge_[~(merge_['time'].str.startswith('-'))]
        merge_.index = range(merge_.shape[0])
        file_path = os.path.splitext(self.path)
        new_path = str(file_path[0] + '时间差' + '.xlsx')
        print(new_path)
        self.plot_data(merge_)
        merge_.to_excel(new_path, index=False)
        with xw.App(visible=False) as app:
            wb = app.books.open(new_path)
            sht = wb.sheets['Sheet1']
            sht.autofit()
            for n, v in enumerate(merge_['second']):
                if v > 150:
                    station = f'D{n}'
                    sht.range(station).color = (135, 206, 250)
            wb.save()
            wb.close()

    def plot_data(self, df):
        fig = go.Figure()
        fig.add_trace(go.Box(
            y=df['second'],
            name='时间差',
            boxpoints='all',
            jitter=0.2,
            opacity=0.8,
            pointpos=0

        ))
        fig.update_layout(
            title={'text': 'SN_pass_fail_time', 'x': 0.5},
            legend_title_text='设备标识',

        )
        fig.show()


if __name__ == "__main__":
    start = time.time()
    iT = Hand_time()
    iT.clear_data()
    print(f'程序运行时间{round(time.time() - start, 2)}秒')
